﻿namespace AnyOffice.DAL
{
    using AnyOffice.Components;
    using AnyOffice.Model;
    using Microsoft.ApplicationBlocks.Data;
    using System;
    using System.Data;
    using System.Data.SqlClient;
    using System.Text;

    public class VoteDao
    {
        public bool Delete(SqlConnection conn, int voteId)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("SET NOCOUNT ON ");
            builder.Append("DELETE FROM [dbo].[Vote_Item] ");
            builder.Append("WHERE ");
            builder.Append("\t[VoteId] = @VoteId ");
            builder.Append("DELETE FROM [dbo].[Vote] ");
            builder.Append("WHERE ");
            builder.Append("\t[VoteId] = @VoteId ");
            SqlParameter[] commandParameters = new SqlParameter[] { new SqlParameter("@VoteId", SqlDbType.Int) };
            commandParameters[0].Value = voteId;
            SqlHelper.ExecuteNonQuery(conn, CommandType.Text, builder.ToString(), commandParameters);
            return true;
        }

        public int Insert(SqlConnection conn, VoteData data)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("SET NOCOUNT ON ");
            builder.Append("INSERT INTO [dbo].[Vote] ");
            builder.Append("( ");
            builder.Append("\t[ToId], ");
            builder.Append("\t[UserId], ");
            builder.Append("\t[Subject], ");
            builder.Append("\t[Content], ");
            builder.Append("\t[Type], ");
            builder.Append("\t[MaxNum], ");
            builder.Append("\t[ViewPriv], ");
            builder.Append("\t[IsAnonymity], ");
            builder.Append("\t[AddTime], ");
            builder.Append("\t[BeginDate], ");
            builder.Append("\t[EndDate], ");
            builder.Append("\t[Readers] ");
            builder.Append(") ");
            builder.Append("VALUES ");
            builder.Append("( ");
            builder.Append("\t@ToId, ");
            builder.Append("\t@UserId, ");
            builder.Append("\t@Subject, ");
            builder.Append("\t@Content, ");
            builder.Append("\t@Type, ");
            builder.Append("\t@MaxNum, ");
            builder.Append("\t@ViewPriv, ");
            builder.Append("\t@IsAnonymity, ");
            builder.Append("\t@AddTime, ");
            builder.Append("\t@BeginDate, ");
            builder.Append("\t@EndDate, ");
            builder.Append("\t@Readers ");
            builder.Append(") ");
            builder.Append("SELECT SCOPE_IDENTITY() AS RetValue ");
            SqlParameter[] commandParameters = new SqlParameter[12];
            commandParameters[0] = new SqlParameter("@ToId", SqlDbType.NText);
            commandParameters[0].Value = data.ToId;
            commandParameters[1] = new SqlParameter("@UserId", SqlDbType.NVarChar, 20);
            commandParameters[1].Value = data.UserId;
            commandParameters[2] = new SqlParameter("@Subject", SqlDbType.NVarChar, 200);
            commandParameters[2].Value = data.Subject;
            commandParameters[3] = new SqlParameter("@Content", SqlDbType.NText);
            if (data.ContentIsNull)
            {
                commandParameters[3].Value = DBNull.Value;
            }
            else
            {
                commandParameters[3].Value = data.Content;
            }
            commandParameters[4] = new SqlParameter("@Type", SqlDbType.Int);
            commandParameters[4].Value = data.Type;
            commandParameters[5] = new SqlParameter("@MaxNum", SqlDbType.Int);
            commandParameters[5].Value = data.MaxNum;
            commandParameters[6] = new SqlParameter("@ViewPriv", SqlDbType.Int);
            commandParameters[6].Value = data.ViewPriv;
            commandParameters[7] = new SqlParameter("@IsAnonymity", SqlDbType.Bit);
            commandParameters[7].Value = data.IsAnonymity;
            commandParameters[8] = new SqlParameter("@AddTime", SqlDbType.DateTime);
            commandParameters[8].Value = data.AddTime;
            commandParameters[9] = new SqlParameter("@BeginDate", SqlDbType.DateTime);
            if (data.BeginDateIsNull)
            {
                commandParameters[9].Value = DBNull.Value;
            }
            else
            {
                commandParameters[9].Value = data.BeginDate;
            }
            commandParameters[10] = new SqlParameter("@EndDate", SqlDbType.DateTime);
            if (data.EndDateIsNull)
            {
                commandParameters[10].Value = DBNull.Value;
            }
            else
            {
                commandParameters[10].Value = data.EndDate;
            }
            commandParameters[11] = new SqlParameter("@Readers", SqlDbType.NText);
            if (data.ReadersIsNull)
            {
                commandParameters[11].Value = DBNull.Value;
            }
            else
            {
                commandParameters[11].Value = data.Readers;
            }
            object obj2 = SqlHelper.ExecuteScalar(conn, CommandType.Text, builder.ToString(), commandParameters);
            if (obj2 != null)
            {
                return Convert.ToInt32(obj2);
            }
            return -1;
        }

        public void Manage(SqlConnection conn, int voteId, int operation)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("SET NOCOUNT ON ");
            builder.Append("IF @Operation=0 ");
            builder.Append("\tUPDATE ");
            builder.Append("\t\t[dbo].[Vote] ");
            builder.Append("\tSET ");
            builder.Append("\t\t[BeginDate] = CAST(CAST(year(GETDATE()) AS varchar)  + '-' + CAST(month(GETDATE()) AS varchar) + '-' + CAST(day(GETDATE()) AS varchar) AS datetime), ");
            builder.Append("\t\t[EndDate] = NULL ");
            builder.Append("\tWHERE ");
            builder.Append("\t\t[VoteId] = @VoteId ");
            builder.Append("IF @Operation=1 ");
            builder.Append("\tUPDATE ");
            builder.Append("\t\t[dbo].[Vote] ");
            builder.Append("\tSET ");
            builder.Append("\t\t[EndDate] = CAST(CAST(year(DATEADD(day,-1,GETDATE())) AS varchar)  + '-' + CAST(month(DATEADD(day,-1,GETDATE())) AS varchar) + '-' + CAST(day(DATEADD(day,-1,GETDATE())) AS varchar) AS datetime) ");
            builder.Append("\tWHERE ");
            builder.Append("\t\t[VoteId] = @VoteId ");
            builder.Append("IF @Operation=2 ");
            builder.Append("\tUPDATE ");
            builder.Append("\t\t[dbo].[Vote] ");
            builder.Append("\tSET ");
            builder.Append("\t\t[EndDate] = NULL ");
            builder.Append("\tWHERE ");
            builder.Append("\t\t[VoteId] = @VoteId ");
            SqlParameter[] commandParameters = new SqlParameter[2];
            commandParameters[0] = new SqlParameter("@VoteId", SqlDbType.Int);
            commandParameters[0].Value = voteId;
            commandParameters[1] = new SqlParameter("@Operation", SqlDbType.Int);
            commandParameters[1].Value = operation;
            SqlHelper.ExecuteNonQuery(conn, CommandType.Text, builder.ToString(), commandParameters);
        }

        public DataTable SelectByVoteId(SqlConnection conn, int voteId)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("SELECT ");
            builder.Append("\t[Vote].[VoteId], ");
            builder.Append("\t[Vote].[ToId],\t ");
            builder.Append("\t[Vote].[UserId], ");
            builder.Append("\t[Vote].[Subject], ");
            builder.Append("\t[Vote].[Content], ");
            builder.Append("\t[Vote].[Type], ");
            builder.Append("\t[Vote].[MaxNum], ");
            builder.Append("\t[Vote].[ViewPriv], ");
            builder.Append("\t[Vote].[IsAnonymity], ");
            builder.Append("\t[Vote].[AddTime], ");
            builder.Append("\t[Vote].[BeginDate], ");
            builder.Append("\t[Vote].[EndDate], ");
            builder.Append("\t[Vote].[Readers], ");
            builder.Append("\tdbo.GetStatus([Vote].[BeginDate], [Vote].[EndDate], GETDATE()) AS Status, ");
            builder.Append("\tdbo.GetDeptNames(CAST([Vote].[ToId] AS nvarchar(2000))) AS ToName, ");
            builder.Append("\t[Users].[UserName], ");
            builder.Append("\t[Department].[DeptName] ");
            builder.Append("FROM ");
            builder.Append("\t[dbo].[Vote], ");
            builder.Append("\t[dbo].[Users], ");
            builder.Append("\t[dbo].[Department] ");
            builder.Append("WHERE ");
            builder.Append("\t[Vote].[VoteId] = @VoteId ");
            builder.Append("\tAND [Vote].[UserId] = [Users].[UserId] ");
            builder.Append("\tAND [Users].[DeptId] = [Department].[DeptId] ");
            SqlParameter[] commandParameters = new SqlParameter[] { new SqlParameter("@VoteId", SqlDbType.Int) };
            commandParameters[0].Value = voteId;
            DataSet set = SqlHelper.ExecuteDataset(conn, CommandType.Text, builder.ToString(), commandParameters);
            set.Tables[0].TableName = "Vote";
            return set.Tables[0];
        }

        public DataTable SelectCurrentPaged(SqlConnection conn, int pagesize, int pageindex, bool docount, int deptId)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("SET NOCOUNT ON ");
            builder.Append("IF(@docount=1)\t ");
            builder.Append("\tSELECT COUNT([VoteId]) FROM [dbo].[Vote] ");
            builder.Append("\tWHERE ");
            builder.Append("\t\t(CAST([ToId] AS nvarchar(2000)) = 'ALL_DEPT' OR CHARINDEX(',' + CAST(@DeptId AS nvarchar) + ',',',' + CAST([ToId] AS nvarchar(2000)) + ',') > 0) ");
            builder.Append("\t\tAND ");
            builder.Append("\t\t( ");
            builder.Append("\t\t\t([BeginDate] IS NULL OR [BeginDate] <= CAST(YEAR(GETDATE()) AS nvarchar) + '-' + CAST(MONTH(GETDATE()) AS nvarchar) + '-' + CAST(DAY(GETDATE()) AS nvarchar)) ");
            builder.Append("\t\t\tAND ([EndDate] IS NULL OR [EndDate] >= CAST(YEAR(GETDATE()) AS nvarchar) + '-' + CAST(MONTH(GETDATE()) AS nvarchar) + '-' + CAST(DAY(GETDATE()) AS nvarchar)) ");
            builder.Append("\t\t)\t ");
            builder.Append("ELSE ");
            builder.Append("\tBEGIN ");
            builder.Append("\t\tDECLARE @IndexTable TABLE(ID int identity(1,1),PK int) ");
            builder.Append("\t\tDECLARE @PageLowerBound int ");
            builder.Append("\t\tDECLARE @PageUpperBound int ");
            builder.Append("\t\tSET @PageLowerBound=(@pageindex-1)*@pagesize ");
            builder.Append("\t\tSET @PageUpperBound=@PageLowerBound+@pagesize ");
            builder.Append("\t\tSET rowcount @PageUpperBound ");
            builder.Append("\t\t ");
            builder.Append("\t\tINSERT INTO @IndexTable(PK) SELECT [VoteId] FROM [dbo].[Vote] ");
            builder.Append("\t\tWHERE ");
            builder.Append("\t\t\t(CAST([ToId] AS nvarchar(2000)) = 'ALL_DEPT' OR CHARINDEX(',' + CAST(@DeptId AS nvarchar) + ',',',' + CAST(ToId AS nvarchar(2000)) + ',') > 0) ");
            builder.Append("\t\t\tAND ");
            builder.Append("\t\t\t( ");
            builder.Append("\t\t\t\t([BeginDate] IS NULL OR [BeginDate] <= CAST(YEAR(GETDATE()) AS nvarchar) + '-' + CAST(MONTH(GETDATE()) AS nvarchar) + '-' + CAST(DAY(GETDATE()) AS nvarchar)) ");
            builder.Append("\t\t\t\tAND ([EndDate] IS NULL OR [EndDate] >= CAST(YEAR(GETDATE()) AS nvarchar) + '-' + CAST(MONTH(GETDATE()) AS nvarchar) + '-' + CAST(DAY(GETDATE()) AS nvarchar)) ");
            builder.Append("\t\t\t) ");
            builder.Append("\t\tORDER BY ");
            builder.Append("\t\t\t[VoteId] DESC ");
            builder.Append("\t\t ");
            builder.Append("\t\tSELECT\t\t\t ");
            builder.Append("\t\t\t[Vote].[VoteId], ");
            builder.Append("\t\t\t[Vote].[ToId],\t\t\t ");
            builder.Append("\t\t\t[Vote].[UserId], ");
            builder.Append("\t\t\t[Vote].[Subject], ");
            builder.Append("\t\t\t[Vote].[Content], ");
            builder.Append("\t\t\t[Vote].[Type], ");
            builder.Append("\t\t\t[Vote].[MaxNum], ");
            builder.Append("\t\t\t[Vote].[ViewPriv], ");
            builder.Append("\t\t\t[Vote].[IsAnonymity], ");
            builder.Append("\t\t\t[Vote].[AddTime], ");
            builder.Append("\t\t\t[Vote].[BeginDate], ");
            builder.Append("\t\t\t[Vote].[EndDate], ");
            builder.Append("\t\t\t[Vote].[Readers], ");
            builder.Append("\t\t\tdbo.GetDeptNames(CAST([Vote].[ToId] AS nvarchar(2000))) AS ToName, ");
            builder.Append("\t\t\tdbo.GetStatus([Vote].[BeginDate],[Vote].[EndDate],GETDATE()) AS Status, ");
            builder.Append("\t\t\t[Users].[UserName], ");
            builder.Append("\t\t\t[Department].[DeptName], ");
            builder.Append("\t\t\t@pageindex AS pageindex ");
            builder.Append("\t\tFROM ");
            builder.Append("\t\t\t[dbo].[Vote], ");
            builder.Append("\t\t\t[dbo].[Users], ");
            builder.Append("\t\t\t[dbo].[Department], ");
            builder.Append("\t\t\t@IndexTable t\t ");
            builder.Append("\t\tWHERE ");
            builder.Append("\t\t\t[Vote].[UserId] = [Users].[UserId] ");
            builder.Append("\t\t\tAND [Users].[DeptId] = [Department].[DeptId] ");
            builder.Append("\t\t\tAND [Vote].[VoteId] = t.PK\t ");
            builder.Append("\t\t\tAND t.ID>@PageLowerBound  ");
            builder.Append("\t\t\tAND t.ID<=@PageUpperBound ");
            builder.Append("\t\tORDER BY ");
            builder.Append("\t\t\t[Vote].[VoteId] DESC ");
            builder.Append("\tEND ");
            SqlParameter[] commandParameters = new SqlParameter[4];
            commandParameters[0] = new SqlParameter("@pagesize", SqlDbType.Int);
            commandParameters[0].Value = pagesize;
            commandParameters[1] = new SqlParameter("@pageindex", SqlDbType.Int);
            commandParameters[1].Value = pageindex;
            commandParameters[2] = new SqlParameter("@docount", SqlDbType.Bit);
            commandParameters[2].Value = docount;
            commandParameters[3] = new SqlParameter("@DeptId", SqlDbType.Int);
            commandParameters[3].Value = deptId;
            DataSet set = SqlHelper.ExecuteDataset(conn, CommandType.Text, builder.ToString(), commandParameters);
            set.Tables[0].TableName = "Vote";
            return set.Tables[0];
        }

        public DataTable SelectHistoryPaged(SqlConnection conn, int pagesize, int pageindex, bool docount, int deptId)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("SET NOCOUNT ON ");
            builder.Append("IF(@docount=1)\t ");
            builder.Append("\tSELECT COUNT([VoteId]) FROM [dbo].[Vote] ");
            builder.Append("\tWHERE ");
            builder.Append("\t\t(CAST([ToId] AS nvarchar(2000)) = 'ALL_DEPT' OR CHARINDEX(',' + CAST(@DeptId AS nvarchar) + ',',',' + CAST(ToId AS nvarchar(2000)) + ',') > 0) ");
            builder.Append("\t\tAND NOT ");
            builder.Append("\t\t( ");
            builder.Append("\t\t\t([BeginDate] IS NULL OR [BeginDate] <= CAST(YEAR(GETDATE()) AS nvarchar) + '-' + CAST(MONTH(GETDATE()) AS nvarchar) + '-' + CAST(DAY(GETDATE()) AS nvarchar)) ");
            builder.Append("\t\t\tAND ([EndDate] IS NULL OR [EndDate] >= CAST(YEAR(GETDATE()) AS nvarchar) + '-' + CAST(MONTH(GETDATE()) AS nvarchar) + '-' + CAST(DAY(GETDATE()) AS nvarchar)) ");
            builder.Append("\t\t) ");
            builder.Append("ELSE ");
            builder.Append("\tBEGIN ");
            builder.Append("\t\tDECLARE @IndexTable TABLE(ID int identity(1,1),PK int) ");
            builder.Append("\t\tDECLARE @PageLowerBound int ");
            builder.Append("\t\tDECLARE @PageUpperBound int ");
            builder.Append("\t\tSET @PageLowerBound=(@pageindex-1)*@pagesize ");
            builder.Append("\t\tSET @PageUpperBound=@PageLowerBound+@pagesize ");
            builder.Append("\t\tSET rowcount @PageUpperBound ");
            builder.Append("\t\t ");
            builder.Append("\t\tINSERT INTO @IndexTable(PK) SELECT [VoteId] FROM [dbo].[Vote] ");
            builder.Append("\t\tWHERE ");
            builder.Append("\t\t\t(CAST([ToId] AS nvarchar(2000)) = 'ALL_DEPT' OR CHARINDEX(',' + CAST(@DeptId AS nvarchar) + ',',',' + CAST(ToId AS nvarchar(2000)) + ',') > 0) ");
            builder.Append("\t\t\tAND NOT ");
            builder.Append("\t\t\t( ");
            builder.Append("\t\t\t\t([BeginDate] IS NULL OR [BeginDate] <= CAST(YEAR(GETDATE()) AS nvarchar) + '-' + CAST(MONTH(GETDATE()) AS nvarchar) + '-' + CAST(DAY(GETDATE()) AS nvarchar)) ");
            builder.Append("\t\t\t\tAND ([EndDate] IS NULL OR [EndDate] >= CAST(YEAR(GETDATE()) AS nvarchar) + '-' + CAST(MONTH(GETDATE()) AS nvarchar) + '-' + CAST(DAY(GETDATE()) AS nvarchar)) ");
            builder.Append("\t\t\t) ");
            builder.Append("\t\tORDER BY ");
            builder.Append("\t\t\t[VoteId] DESC ");
            builder.Append("\t\t ");
            builder.Append("\t\tSELECT\t\t\t ");
            builder.Append("\t\t\t[Vote].[VoteId], ");
            builder.Append("\t\t\t[Vote].[ToId],\t\t\t ");
            builder.Append("\t\t\t[Vote].[UserId], ");
            builder.Append("\t\t\t[Vote].[Subject], ");
            builder.Append("\t\t\t[Vote].[Content], ");
            builder.Append("\t\t\t[Vote].[Type], ");
            builder.Append("\t\t\t[Vote].[MaxNum], ");
            builder.Append("\t\t\t[Vote].[ViewPriv], ");
            builder.Append("\t\t\t[Vote].[IsAnonymity], ");
            builder.Append("\t\t\t[Vote].[AddTime], ");
            builder.Append("\t\t\t[Vote].[BeginDate], ");
            builder.Append("\t\t\t[Vote].[EndDate], ");
            builder.Append("\t\t\t[Vote].[Readers], ");
            builder.Append("\t\t\tdbo.GetDeptNames(CAST([Vote].[ToId] AS nvarchar(2000))) AS ToName, ");
            builder.Append("\t\t\tdbo.GetStatus([Vote].[BeginDate],[Vote].[EndDate],GETDATE()) AS Status, ");
            builder.Append("\t\t\t[Users].[UserName], ");
            builder.Append("\t\t\t[Department].[DeptName], ");
            builder.Append("\t\t\t@pageindex AS pageindex ");
            builder.Append("\t\tFROM ");
            builder.Append("\t\t\t[dbo].[Vote], ");
            builder.Append("\t\t\t[dbo].[Users], ");
            builder.Append("\t\t\t[dbo].[Department], ");
            builder.Append("\t\t\t@IndexTable t\t ");
            builder.Append("\t\tWHERE ");
            builder.Append("\t\t\t[Vote].[UserId] = [Users].[UserId] ");
            builder.Append("\t\t\tAND [Users].[DeptId] = [Department].[DeptId] ");
            builder.Append("\t\t\tAND [Vote].[VoteId] = t.PK\t ");
            builder.Append("\t\t\tAND t.ID>@PageLowerBound  ");
            builder.Append("\t\t\tAND t.ID<=@PageUpperBound ");
            builder.Append("\t\tORDER BY ");
            builder.Append("\t\t\t[Vote].[VoteId] DESC ");
            builder.Append("\tEND ");
            SqlParameter[] commandParameters = new SqlParameter[4];
            commandParameters[0] = new SqlParameter("@pagesize", SqlDbType.Int);
            commandParameters[0].Value = pagesize;
            commandParameters[1] = new SqlParameter("@pageindex", SqlDbType.Int);
            commandParameters[1].Value = pageindex;
            commandParameters[2] = new SqlParameter("@docount", SqlDbType.Bit);
            commandParameters[2].Value = docount;
            commandParameters[3] = new SqlParameter("@DeptId", SqlDbType.Int);
            commandParameters[3].Value = deptId;
            DataSet set = SqlHelper.ExecuteDataset(conn, CommandType.Text, builder.ToString(), commandParameters);
            set.Tables[0].TableName = "Vote";
            return set.Tables[0];
        }

        public DataTable SelectMyTable(SqlConnection conn, int deptId)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("SELECT TOP " + new ConfigManager().VoteNum.ToString());
            builder.Append("\t[VoteId],\t ");
            builder.Append("\t[Subject] ");
            builder.Append("FROM ");
            builder.Append("\t[dbo].[Vote] ");
            builder.Append("WHERE ");
            builder.Append("\t\t(CAST([ToId] AS nvarchar(2000)) = 'ALL_DEPT' OR CHARINDEX(',' + CAST(@DeptId AS nvarchar) + ',',',' + CAST([ToId] AS nvarchar(2000)) + ',') > 0) ");
            builder.Append("\t\tAND ");
            builder.Append("\t\t( ");
            builder.Append("\t\t\t([BeginDate] IS NULL OR [BeginDate] <= CAST(YEAR(GETDATE()) AS nvarchar) + '-' + CAST(MONTH(GETDATE()) AS nvarchar) + '-' + CAST(DAY(GETDATE()) AS nvarchar)) ");
            builder.Append("\t\t\tAND ([EndDate] IS NULL OR [EndDate] >= CAST(YEAR(GETDATE()) AS nvarchar) + '-' + CAST(MONTH(GETDATE()) AS nvarchar) + '-' + CAST(DAY(GETDATE()) AS nvarchar)) ");
            builder.Append("\t\t) ");
            builder.Append("ORDER BY ");
            builder.Append("\t[VoteId] DESC ");
            SqlParameter[] commandParameters = new SqlParameter[] { new SqlParameter("@DeptId", SqlDbType.Int) };
            commandParameters[0].Value = deptId;
            DataSet set = SqlHelper.ExecuteDataset(conn, CommandType.Text, builder.ToString(), commandParameters);
            set.Tables[0].TableName = "Vote";
            return set.Tables[0];
        }

        public DataTable SelectPaged(SqlConnection conn, int pagesize, int pageindex, bool docount)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("SET NOCOUNT ON ");
            builder.Append("IF(@docount=1) ");
            builder.Append("\tSELECT COUNT([VoteId]) FROM [dbo].[Vote] ");
            builder.Append("ELSE ");
            builder.Append("\tBEGIN ");
            builder.Append("\t\tDECLARE @IndexTable TABLE(ID int identity(1,1),PK int) ");
            builder.Append("\t\tDECLARE @PageLowerBound int ");
            builder.Append("\t\tDECLARE @PageUpperBound int ");
            builder.Append("\t\tSET @PageLowerBound=(@pageindex-1)*@pagesize ");
            builder.Append("\t\tSET @PageUpperBound=@PageLowerBound+@pagesize ");
            builder.Append("\t\tSET rowcount @PageUpperBound ");
            builder.Append("\t\t ");
            builder.Append("\t\tINSERT INTO @IndexTable(PK) SELECT [VoteId] FROM [dbo].[Vote] ORDER BY [VoteId] DESC ");
            builder.Append("\t\t ");
            builder.Append("\t\tSELECT\t\t\t ");
            builder.Append("\t\t\t[Vote].[VoteId], ");
            builder.Append("\t\t\t[Vote].[ToId],\t\t\t ");
            builder.Append("\t\t\t[Vote].[UserId], ");
            builder.Append("\t\t\t[Vote].[Subject], ");
            builder.Append("\t\t\t[Vote].[Content], ");
            builder.Append("\t\t\t[Vote].[Type], ");
            builder.Append("\t\t\t[Vote].[MaxNum], ");
            builder.Append("\t\t\t[Vote].[ViewPriv], ");
            builder.Append("\t\t\t[Vote].[IsAnonymity], ");
            builder.Append("\t\t\t[Vote].[AddTime], ");
            builder.Append("\t\t\t[Vote].[BeginDate], ");
            builder.Append("\t\t\t[Vote].[EndDate], ");
            builder.Append("\t\t\t[Vote].[Readers], ");
            builder.Append("\t\t\tdbo.GetDeptNames([Vote].[ToId]) AS ToName, ");
            builder.Append("\t\t\tdbo.GetStatus([Vote].[BeginDate],[Vote].[EndDate],GETDATE()) AS Status, ");
            builder.Append("\t\t\t[Users].[UserName], ");
            builder.Append("\t\t\t[Department].[DeptName], ");
            builder.Append("\t\t\t@pageindex AS pageindex ");
            builder.Append("\t\tFROM ");
            builder.Append("\t\t\t[dbo].[Vote], ");
            builder.Append("\t\t\t[dbo].[Users], ");
            builder.Append("\t\t\t[dbo].[Department], ");
            builder.Append("\t\t\t@IndexTable t\t ");
            builder.Append("\t\tWHERE ");
            builder.Append("\t\t\t[Vote].[UserId] = [Users].[UserId] ");
            builder.Append("\t\t\tAND [Users].[DeptId] = [Department].[DeptId] ");
            builder.Append("\t\t\tAND [Vote].[VoteId] = t.PK\t ");
            builder.Append("\t\t\tAND t.ID>@PageLowerBound  ");
            builder.Append("\t\t\tAND t.ID<=@PageUpperBound ");
            builder.Append("\t\tORDER BY ");
            builder.Append("\t\t\t[Vote].[VoteId] DESC ");
            builder.Append("\tEND ");
            SqlParameter[] commandParameters = new SqlParameter[3];
            commandParameters[0] = new SqlParameter("@pagesize", SqlDbType.Int);
            commandParameters[0].Value = pagesize;
            commandParameters[1] = new SqlParameter("@pageindex", SqlDbType.Int);
            commandParameters[1].Value = pageindex;
            commandParameters[2] = new SqlParameter("@docount", SqlDbType.Bit);
            commandParameters[2].Value = docount;
            DataSet set = SqlHelper.ExecuteDataset(conn, CommandType.Text, builder.ToString(), commandParameters);
            set.Tables[0].TableName = "Vote";
            return set.Tables[0];
        }

        public bool Update(SqlConnection conn, VoteData data)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("SET NOCOUNT ON ");
            builder.Append("UPDATE ");
            builder.Append("\t[dbo].[Vote] ");
            builder.Append("SET ");
            builder.Append("\t[ToId] = @ToId, ");
            builder.Append("\t[UserId] = @UserId, ");
            builder.Append("\t[Subject] = @Subject, ");
            builder.Append("\t[Content] = @Content, ");
            builder.Append("\t[Type] = @Type, ");
            builder.Append("\t[MaxNum] = @MaxNum, ");
            builder.Append("\t[ViewPriv] = @ViewPriv, ");
            builder.Append("\t[IsAnonymity] = @IsAnonymity, ");
            builder.Append("\t[BeginDate] = @BeginDate, ");
            builder.Append("\t[EndDate] = @EndDate ");
            builder.Append("WHERE ");
            builder.Append("\t[VoteId] = @VoteId ");
            SqlParameter[] commandParameters = new SqlParameter[13];
            commandParameters[0] = new SqlParameter("@VoteId", SqlDbType.Int);
            commandParameters[0].Value = data.VoteId;
            commandParameters[1] = new SqlParameter("@ToId", SqlDbType.NText);
            commandParameters[1].Value = data.ToId;
            commandParameters[2] = new SqlParameter("@UserId", SqlDbType.NVarChar, 20);
            commandParameters[2].Value = data.UserId;
            commandParameters[3] = new SqlParameter("@Subject", SqlDbType.NVarChar, 200);
            commandParameters[3].Value = data.Subject;
            commandParameters[4] = new SqlParameter("@Content", SqlDbType.NText);
            if (data.ContentIsNull)
            {
                commandParameters[4].Value = DBNull.Value;
            }
            else
            {
                commandParameters[4].Value = data.Content;
            }
            commandParameters[5] = new SqlParameter("@Type", SqlDbType.Int);
            commandParameters[5].Value = data.Type;
            commandParameters[6] = new SqlParameter("@MaxNum", SqlDbType.Int);
            commandParameters[6].Value = data.MaxNum;
            commandParameters[7] = new SqlParameter("@ViewPriv", SqlDbType.Int);
            commandParameters[7].Value = data.ViewPriv;
            commandParameters[8] = new SqlParameter("@IsAnonymity", SqlDbType.Bit);
            commandParameters[8].Value = data.IsAnonymity;
            commandParameters[9] = new SqlParameter("@AddTime", SqlDbType.DateTime);
            commandParameters[9].Value = data.AddTime;
            commandParameters[10] = new SqlParameter("@BeginDate", SqlDbType.DateTime);
            if (data.BeginDateIsNull)
            {
                commandParameters[10].Value = DBNull.Value;
            }
            else
            {
                commandParameters[10].Value = data.BeginDate;
            }
            commandParameters[11] = new SqlParameter("@EndDate", SqlDbType.DateTime);
            if (data.EndDateIsNull)
            {
                commandParameters[11].Value = DBNull.Value;
            }
            else
            {
                commandParameters[11].Value = data.EndDate;
            }
            commandParameters[12] = new SqlParameter("@Readers", SqlDbType.NText);
            if (data.ReadersIsNull)
            {
                commandParameters[12].Value = DBNull.Value;
            }
            else
            {
                commandParameters[12].Value = data.Readers;
            }
            SqlHelper.ExecuteNonQuery(conn, CommandType.Text, builder.ToString(), commandParameters);
            return true;
        }
    }
}

